Create Replication Job Dialog

Replication copies a source table into a target table, according to the chosen algorithm and strategy. A replicated table in the target schema (normally the DWH but it might be also a different schema) is not normally used by the optimizer.

Choose the parameters for a Replication Job to be created

• Batch replication: special type of materialization for optimizations of the type TABLE.
• History update: a "slowly changing dimension (type 2)" job. Extensive documentation can be found here.
• Upsert update: intended to provide an easy way to replicate a source table in a table stored in the Analytical Storage. It is a more sophisticated approach than complete replication and incremental replication because it can perform UPDATE operations on existing rows and INSERT operations on new ones. In contrast to history update, there will never be more than one entry for a row with a specific set of values for key columns. Additional timestamp columns, like the ones that automatically come with history update, will not be created.
• Copy over source: a flexible and simple way to replicate data. It allows to define a table name and select the cleanup policy: drop table and recreate, delete all rows or truncate the rows (recommended for Redshift data sources).

Advanced options:

• Parallel runs allowed: the limit of simultaneous job runs: unlimited (-1), job execution not allowed (0)

Batch Replication Job

In order to set up a batch replication job, the following values should be specified:

• Destination table: an existing materialized table or new table
• Identity expression (optional): an expression to avoid data duplication.

Upsert Update

In order to set up an upsert replication job, the following values should be specified:

• Table in Analytical Storage: The table that will store the local content.
• Key columns: Key columns to decide whether UPDATE (on existing rows) or INSERT (on new rows) has to be performed. Key columns cannot be simultaneously used in the update columns list and they will not be updated.
• Update columns: Set of columns which is used to determine which columns will be updated on existing rows.
• Invert update columns: Determines which columns exactly will be updated. Either all columns from update columns list (when not checked) or all columns except these (when checked) will be updated.
• Check Max Field (or expression): Check field or expression in update statement.
• Value if null: Default value if check field or expression is null.
• Surrogate Key Type: How to calculate the surrogate key: can have values COUNTER (column type long) or UUID (column type string). NULL (Default) is not generate the surrogate key.
• Surrogate Key Column Name: If Surrogate Key Type is not null, an additional column with surrogate key will be added to the target table. Type of the column depends on the Surrogate Key Type.

Copy Over Source

In order to set up a copy over source replication job, the following values should be specified:

• Data source: The data source to which the data will be replicated. Please note that for a data source to be enlisted its model property importer.defaultSchema must be set.
• New storage table: The table that will store the local content.
• Cleanup method:
  • Drop and recreate table
  • Delete all rows from the table
  • Truncate all rows from the table (recommended for Redshift)

Reference manual pages:

• Jobs and Schedules
  • Creating schedules
  • Chained schedules
  • Managing schedules
  • Managing jobs